package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.User;
import cn.edu.sdjzu.xg.bysj.domain.authority.Actor;
import cn.edu.sdjzu.xg.bysj.service.ActorService;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j
public final class UserDao {
    //新建UserDao的实例对象
    private static UserDao userDao = new UserDao();
    //预编译语句
    private PreparedStatement preparedStatement;
    //结果集
    private ResultSet resultSet;
    private UserDao() {
    }
    //获得实例
    public static UserDao getInstance() {
        return userDao;
    }

    public Collection<User> findAll(Connection connection, Collection<Condition> conditions, Pagination pagination) throws SQLException {
        Collection<User> users = new TreeSet<User>();
        //构建查找sql语句
        String sql_select_all = "SELECT * FROM student ";
        if (conditions != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(conditions));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            int totalNum = CommonDao.getInstance().count(connection,"user");
            //如果带有分页请求，则根据请求创建对应的LIMIT子句
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        //预编sql语句
        preparedStatement = connection.prepareStatement(sql_select_all);
        //执行，获得结果集
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            Actor actor = TeacherService.getInstance().find(resultSet.getInt("actor_id"));
            String time = resultSet.getString("lastLoginTime");
            LocalDateTime lastLoginTime = LocalDateTime.parse(time);
            User foundUser = new User(id,username,password,lastLoginTime,actor);
            users.add(foundUser);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return users;
    }

    public Collection<User> findAll(Connection connection) throws SQLException {
        preparedStatement = connection.prepareStatement("SELECT * FROM user");
        resultSet = preparedStatement.executeQuery();
        Collection<User> users = new TreeSet<User>();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            Actor actor = TeacherService.getInstance().find(resultSet.getInt("actor_id"));
            String time = resultSet.getString("lastLoginTime");
            LocalDateTime lastLoginTime = LocalDateTime.parse(time);
            User foundUser = new User(id,username,password,lastLoginTime,actor);
            users.add(foundUser);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return users;
    }
    /**
     * 根据id查找相应的对象
     * @param id 查找对象的元组id
     * @return 返回查找到的对象
     * @throws SQLException
     */
    public User find(Connection connection,Integer id) throws SQLException {
        User foundUser = null;
        //预编译sql语句
        preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE id=?");
        //为sql语句参数赋值，查找的元组的id值
        preparedStatement.setInt(1,id);
        //执行sql语句，获得结果集
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //拿取id，用户名，密码，上次登录时间的值
            int user_id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            Actor actor = TeacherService.getInstance().find(resultSet.getInt("actor_id"));
            Timestamp time = resultSet.getTimestamp("lastLoginTime");
            //将时间戳转换为本地时间格式
            LocalDateTime lastLoginTime = time.toLocalDateTime();
            foundUser = new User(id,username,password,lastLoginTime,actor);
        }

        //关闭数据库连接
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的对象
        return foundUser;
    }

    public boolean update(Connection connection,User user) throws SQLException {
        preparedStatement = connection.prepareStatement("UPDATE user SET username=?,password=? WHERE id=?");
        preparedStatement.setInt(3,user.getId());
        preparedStatement.setString(1,user.getUsername());
        preparedStatement.setString(2,user.getPassword());
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected > 0;
    }

    /**
     * TeacherService.add在调用本方法前，还会调用TeacherDao.add的方法
     * 两次对Dao的调用保证是同一个链接对象，才能将他们设置到同一个事务当中
     * @param user 要增加到数据库的user对象
     * @param connection 来自Service的链接对象
     * @return 成功增加：true，失败：false
     * @throws SQLException
     */
    public boolean add(Connection connection,User user) throws SQLException {
        //预编译sql语句，lastLongTime为时间戳类型，无需手动输入
        this.preparedStatement = connection.prepareStatement("INSERT INTO user (username,password,actor_id)VALUES (?,?,?)");
        preparedStatement.setString(1,user.getUsername());
        preparedStatement.setString(2,user.getPassword());
        preparedStatement.setInt(3,user.getActor().getId());
        //执行sql语句并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        //事务未结束，不能释放资源
        return rowAffected>0;
    }

    public boolean delete(Connection connection,Integer id) throws SQLException {
        preparedStatement = connection.prepareStatement("DELETE FROM user WHERE id=?");
        preparedStatement.setInt(1,id);
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected > 0;
    }

    public Actor login(Connection connection, User user) throws SQLException {
        //创建Actor类型的引用，暂不创建对象
        Actor currentActor = null;
        String sql_login = "SELECT * FROM user WHERE username =? and password=?";
        PreparedStatement pstmt_login =
                connection.prepareStatement(sql_login);
        pstmt_login.setString(1, user.getUsername());

        pstmt_login.setString(2, user.getPassword());
        ResultSet resultSet_login = pstmt_login.executeQuery();
        //如果表中存在id对应的记录，则获得表中的actor_id，并获得对应的Actor对象
        if (resultSet_login.next()) {
            int actor_id = resultSet_login.getInt("actor_id");
            currentActor = ActorService.getInstance().find(actor_id);
        }
        return currentActor;
    }
}
